﻿using Corina.Datamodel.Entities;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;

namespace Corina.DataContext.Service
{
    public class UserHypotaxisService : ServiceBase
    {
        public DataTable GetSubusers(string connectionString, int superiorId, int index, int size, ref int count)
        {
            List<SqlParameter> paras = new List<SqlParameter>();

            StringBuilder ctestring = new StringBuilder();
            ctestring.Append(";WITH cte AS ( ");
            ctestring.Append("	SELECT tuh.Id, tuh.Subid, tuh.SuperiorId FROM t_user_hypotaxis AS tuh ");
            ctestring.Append("	WHERE 1=1 ");
            if (superiorId > 0)
            {
                ctestring.Append(" AND tuh.SuperiorId = @Supid ");
                paras.Add(new SqlParameter("@Supid", superiorId));
            }
            ctestring.Append("	UNION ALL ");
            ctestring.Append("	SELECT tuh_u.Id, tuh_u.Subid, tuh_u.SuperiorId ");
            ctestring.Append("	FROM t_user_hypotaxis tuh_u  ");
            ctestring.Append("	JOIN cte AS tuh_2 ON tuh_u.SuperiorId = tuh_2.Subid ) ");

            StringBuilder builder = new StringBuilder();
            string qstring = "SELECT * FROM ( ";
            string cstring = "SELECT COUNT(*) FROM ( ";

            /*
            builder.Append("SELECT ROW_NUMBER() OVER (ORDER BY tuh.Subid ASC) AS ROW_NUMBER,tuh.*, tuSub.Name AS Subname,tuSup.Name AS Supname ");
            builder.Append(" FROM t_user_hypotaxis AS tuh ");
            builder.Append("LEFT JOIN t_user AS tuSub ON tuSub.Id = tuh.Subid ");
            builder.Append("LEFT JOIN t_user AS tuSup ON tuSup.Id = tuh.SuperiorId ");
            builder.Append("WHERE 1=1 ");
            */

            builder.Append("SELECT ROW_NUMBER() OVER (ORDER BY c.SuperiorId DESC, c.Subid DESC) AS ROW_NUMBER,tu.Id, tu.Name, tu.Account, tu.RegisterTime, tu.Linkman ");
            
            builder.Append("FROM t_user AS tu ");

            builder.Append(" LEFT JOIN cte AS c ON tu.Id = c.Subid ");

            builder.Append(" WHERE tu.Id IN (SELECT cte.Subid FROM cte) ");

            builder.Append(" ) AS QUERY ");

            count = DbOperator.GetCount(connectionString, string.Format("{0}{1}{2}", ctestring, cstring, builder), paras.ToArray());

            builder.Append("WHERE QUERY.[ROW_NUMBER] BETWEEN @Index + 1 AND @Index + @Size ");
            paras.Add(new SqlParameter("@Index", (index - 1) * size));
            paras.Add(new SqlParameter("@Size", size));
            builder.Append("ORDER BY QUERY.[ROW_NUMBER]");

            DataTable table = DbOperator.GetDataTable(connectionString, string.Format("{0}{1}{2}", ctestring, qstring, builder), paras.ToArray());
            return table;
        }

        public DataTable GetUnallocateUsers(string connectionString, string key, int index, int size, ref int count)
        {
            StringBuilder builder = new StringBuilder();
            string qstring = "SELECT * FROM ( ";
            string cstring = "SELECT COUNT(*) FROM ( ";
            List<SqlParameter> paras = new List<SqlParameter>();

            builder.Append("SELECT ROW_NUMBER() OVER (ORDER BY tu.Id DESC) AS ROW_NUMBER,tu.Id, tu.Account, tu.Name ");
            builder.Append("  FROM t_user AS tu  ");
            builder.Append("WHERE tu.Id NOT IN (SELECT tuh.Subid FROM t_user_hypotaxis AS tuh) ");
            builder.Append(" AND (tu.Account LIKE @Key OR tu.Name LIKE @Key )");

            paras.Add(new SqlParameter("@Key", string.Format("%{0}%", key)));

            builder.Append(" ) AS QUERY ");

            count = DbOperator.GetCount(connectionString, string.Format("{0}{1}", cstring, builder), paras.ToArray());

            builder.Append("WHERE query.[ROW_NUMBER] BETWEEN @Index + 1 AND @Index + @Size ");
            paras.Add(new SqlParameter("@Index", (index - 1) * size));
            paras.Add(new SqlParameter("@Size", size));
            builder.Append("ORDER BY query.[ROW_NUMBER]");

            DataTable table = DbOperator.GetDataTable(connectionString, string.Format("{0}{1}", qstring, builder), paras.ToArray());
            return table;
        }

        public bool Update(string connectionString, int superiorId, params int[] subids)
        {
            if (subids.Count() == 0)
            {
                return false;
            }
            List<SqlParameter> paras = new List<SqlParameter>();

            //IEnumerable<int> ids = subids.Split(',').Select(t => Convert.ToInt32(t));

            IEnumerable<int> existIds = GetExistSubid(connectionString, superiorId); //DbOperator.GetEntities<UserHypotaxisInfo>(connectionString, new List<string> { "SuperiorId" }, new List<object>() { superiorId }).Select(t => t.Subid);

            List<int> insertIds = subids.Where(t => !existIds.Contains(t)).ToList();

            List<int> deleteIds = existIds.Where(t => !subids.Contains(t)).ToList();

            List<string> queryString = new List<string>();

            if (insertIds.Count > 0)
            {
                for (var i = 0; i < insertIds.Count; i++)
                {
                    queryString.Add(string.Format("INSERT INTO t_user_hypotaxis(SuperiorId,Subid) VALUES(@Supid,@insert_Subid_{0})", i));
                    paras.Add(new SqlParameter("@insert_Subid_" + i, insertIds[i]));
                }
            }

            if (deleteIds.Count > 0)
            {
                for (var i = 0; i < deleteIds.Count; i++)
                {
                    queryString.Add(string.Format("DELETE FROM t_user_hypotaxis WHERE SuperiorId=@Supid AND Subid=@delete_Subid_{0}", i));
                    paras.Add(new SqlParameter("@delete_Subid_" + i, deleteIds[i]));
                }
            }

            paras.Add(new SqlParameter("@Supid", superiorId));
            if (queryString.Count == 0)
            {
                return true;
            }
            int exeResult = DbOperator.ExexuteCommand(connectionString, queryString, paras.ToArray());

            return exeResult > 0;
        }

        public IEnumerable<int> GetExistSubid(string connectionString, int superiorId)
        {
            List<SqlParameter> paras = new List<SqlParameter>();

            StringBuilder ctestring = new StringBuilder();
            ctestring.Append(";WITH cte AS ( ");
            ctestring.Append("	SELECT tuh.Id, tuh.Subid, tuh.SuperiorId FROM t_user_hypotaxis AS tuh ");
            ctestring.Append("	WHERE 1=1 ");
            ctestring.Append(" AND tuh.SuperiorId = @Supid ");
            ctestring.Append("	UNION ALL ");
            ctestring.Append("	SELECT tuh_u.Id, tuh_u.Subid, tuh_u.SuperiorId ");
            ctestring.Append("	FROM t_user_hypotaxis tuh_u  ");
            ctestring.Append("	JOIN cte AS tuh_2 ON tuh_u.SuperiorId = tuh_2.Subid ) ");
            ctestring.Append(" SELECT * FROM cte AS c");

            DataTable table = DbOperator.GetDataTable(connectionString, ctestring.ToString(), new SqlParameter("@Supid", superiorId));

            return table.AsEnumerable().Select(t => Convert.ToInt32(t["Subid"]));
        }
    }
}
